#!/usr/bin/env python
#-*-coding:utf-8-*-

import sqlite3 as sl
import sys, os, time

class CykloryDB():
  def __init__(self):
    self.con = None
    try:
      self.con = sl.connect(os.environ["HOME"]+"/.cyklory/cyklory.db")
      self.cur = self.con.cursor()
      self.cur.execute('CREATE TABLE IF NOT EXISTS Subjects(id INTEGER PRIMARY KEY AUTOINCREMENT, subject TEXT)')
      self.cur.execute('CREATE TABLE IF NOT EXISTS Curriculums(id INTEGER PRIMARY KEY AUTOINCREMENT, subject_id INTEGER, name TEXT, type TEXT)')
      self.cur.execute('CREATE TABLE IF NOT EXISTS Walltexts(id INTEGER PRIMARY KEY AUTOINCREMENT, subject_id INTEGER, curriculum_id INTEGER, txt TEXT)')
      self.con.commit()

    except sl.Error, e:
      print "Error %s:" % e.args[0]
      sys.exit(1)

  def exit():
      if self.con:
        self.con.close()
        
  def insert_record(self, table, data):
      command = "INSERT INTO "+table+" VALUES (null"
      if type(data) != list:
        command += ", '"+data+"')"
      else:
        for e in data:
          if type(e) == int:
            command += ", "+str(e)
          else:
            command += ", '"+e+"'"
        command += ")"
      self.cur.execute(command)
      self.con.commit()

  def update_record(self, table, data):
      command = 'UPDATE '+table+' SET txt="'+data[2]+'" WHERE subject_id='+str(data[0])+' AND curriculum_id='+str(data[1])
      self.cur.execute(command)
      self.con.commit()

  def get_records(self, table, columns=None, terms=None):
      command = "SELECT "
      if columns == None:
        command += "*"
      else:
        command += "("
        i = 0
        for c in columns:
          command += c
          if i < (len(columns)-1):
            command += ", "
          i += 1
        command += ")"
      command += " FROM " + table
      if terms != None:
        command += " WHERE"
        i = 0
        for t in terms:
          command += " "+t[0]+"="
          if type(t[1]) == int:
            command += str(t[1])
          else:
            command += '"'+t[1]+'"'
          if i < (len(terms)-1):
            command += " AND"
          i += 1
      self.cur.execute(command)
      return self.cur.fetchall()
